﻿namespace KeppelLand.Controllers
{
    using KeppelLand.Entities;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;


    /// <summary>
    /// Summary description for ReportingPeriodController
    /// </summary>
    public class ReportingPeriodController
    {
        public ReportingPeriodController()
        {
        }

        public static ReportingPeriod GetLockedReportingPeriod()
        {
            ReportingPeriod lockedReportingPeriod = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet("LockedReportingPeriod");
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM ReportingPeriod WHERE Locked = 1;";
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

                da.Fill(ds, "LockedReportingPeriod");
        
                if (ds.Tables["LockedReportingPeriod"].Rows.Count > 0)
                {
                    lockedReportingPeriod = ds.Tables["LockedReportingPeriod"].AsEnumerable().Select(r => new KeppelLand.Entities.ReportingPeriod
                    {
                        Id = r.Field<int>("Id"),
                        EndDate = r.Field<DateTime>("EndDate"),
                        Locked = r.Field<bool>("Locked"),
                        LockedDate = r.Field<DateTime?>("LockedDate")
                    }).FirstOrDefault();
                }
            }

            return lockedReportingPeriod;
        }

        public static ReportingPeriod GetNextReportingPeriod()
        {
            ReportingPeriod lockedReportingPeriod = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet("LockedReportingPeriod");
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM ReportingPeriod WHERE LockedDate IS NULL;";
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

                da.Fill(ds, "LockedReportingPeriod");

                if (ds.Tables["LockedReportingPeriod"].Rows.Count > 0)
                {
                    lockedReportingPeriod = ds.Tables["LockedReportingPeriod"].AsEnumerable().Select(r => new KeppelLand.Entities.ReportingPeriod
                    {
                        Id = r.Field<int>("Id"),
                        EndDate = r.Field<DateTime>("EndDate"),
                        Locked = r.Field<bool>("Locked"),
                        LockedDate = r.Field<DateTime?>("LockedDate")
                    }).FirstOrDefault();
                }
            }

            return lockedReportingPeriod;
        }

        public static List<ReportingPeriodHistory> GetReportingPeriodHistory()
        {
            List<ReportingPeriodHistory> reportingPeriodList = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet("LockedReportingPeriod");
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"
SELECT  DISTINCT
        rp.Id, 
        rp.EndDate, 
        rp.Locked, 
        rp.LockedDate, 
        dbo.ReportingPeriodMonths(rp.Id) AS 'ClaimMonth'
FROM    ReportingPeriod rp
LEFT OUTER JOIN 
        Claim c
        ON rp.Id = c.ReportingPeriodId
WHERE   rp.LockedDate IS NOT NULL 
GROUP BY 
        rp.Id, 
        rp.EndDate, 
        rp.Locked, 
        rp.LockedDate, 
SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 112), 1, 6)
ORDER BY rp.EndDate DESC";

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

                da.Fill(ds, "ReportingPeriod");

                if (ds.Tables["ReportingPeriod"].Rows.Count > 0)
                {
                    reportingPeriodList = ds.Tables["ReportingPeriod"].AsEnumerable().Select(r => new KeppelLand.Entities.ReportingPeriodHistory
                    {
                        Id = r.Field<int>("Id"),
                        EndDate = r.Field<DateTime>("EndDate"),
                        Locked = r.Field<bool>("Locked"),
                        LockedDate = r.Field<DateTime?>("LockedDate"),
                        ClaimMonth = r.Field<string>("ClaimMonth")
                    }).ToList();
                }
            }

            return reportingPeriodList;
        }

        public static ReportingStructure GetReportingStructureByEmployeeMaster(string employeeMaster)
        {
            ReportingStructure reportingStructure = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet("ReportingStructure");
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT EmployeeMaster, COALESCE(RecommenderId2, RecommenderId1) AS 'RecommenderId', COALESCE(ApproverId2, ApproverId1) AS 'ApproverId' FROM ReportingStructure WHERE EmployeeMaster = @EmployeeMaster;";
                cmd.Parameters.AddWithValue("@EmployeeMaster", employeeMaster);

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

                da.Fill(ds, "ReportingStructure");

                if (ds.Tables["ReportingStructure"].Rows.Count > 0)
                {
                    reportingStructure = ds.Tables["ReportingStructure"].AsEnumerable().Select(r => new KeppelLand.Entities.ReportingStructure
                    {
                        EmployeeMaster = r.Field<string>("EmployeeMaster"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        ApproverId = r.Field<string>("ApproverId")
                        //RecommenderId1 = r.Field<string>("RecommenderId1"),
                        //RecommenderId2 = r.Field<string>("RecommenderId2"),
                        //ApproverId1 = r.Field<string>("ApproverId1"),
                        //ApproverId2 = r.Field<string>("ApproverId2")
                    }).FirstOrDefault();
                }
            }

            return reportingStructure;
        }

        public static List<KeppelLand.Entities.ReportingStructure> GetReportingStructureList(string section)
        {
            List<KeppelLand.Entities.ReportingStructure> reportingStructureList = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet("ReportingStructure");
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT  rs.EmployeeMaster, COALESCE(rs.RecommenderId2, rs.RecommenderId1) AS 'RecommenderId', COALESCE(rs.ApproverId2, rs.ApproverId1) AS 'ApproverId', B2E.NameLegal
FROM    ReportingStructure rs
LEFT OUTER JOIN
        B2E
        ON rs.EmployeeMaster = B2E.EmployeeMaster
WHERE   rs.EmployeeMaster IN (SELECT EmployeeMaster FROM B2E WHERE Section = @Section)
ORDER BY B2E.NameLegal;";

                cmd.Parameters.AddWithValue("@Section", section);

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

                da.Fill(ds, "ReportingStructure");

                if (ds.Tables["ReportingStructure"].Rows.Count > 0)
                {
                    //EmployeeMaster	RecommenderId1	RecommenderId2	ApproverId1	ApproverId2

                    reportingStructureList = ds.Tables["ReportingStructure"].AsEnumerable().Select(r => new KeppelLand.Entities.ReportingStructure
                    {
                        EmployeeMaster = r.Field<string>("EmployeeMaster"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        ApproverId = r.Field<string>("ApproverId")

                        //RecommenderId1 = r.Field<string>("RecommenderId1"),
                        //RecommenderId2 = r.Field<string>("RecommenderId2"),
                        //ApproverId1 = r.Field<string>("ApproverId1"),
                        //ApproverId2 = r.Field<string>("ApproverId2")
                    }).ToList();
                }
            }

            return reportingStructureList;
        }

        public static List<KeppelLand.Entities.ReportingStructure> GeAllReportingStructureList()
        {
            List<KeppelLand.Entities.ReportingStructure> reportingStructureList = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet("ReportingStructure");
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT  rs.EmployeeMaster, COALESCE(rs.RecommenderId2, rs.RecommenderId1) AS 'RecommenderId', COALESCE(rs.ApproverId2, rs.ApproverId1) AS 'ApproverId', B2E.NameLegal
FROM    ReportingStructure rs
LEFT OUTER JOIN
        B2E
        ON rs.EmployeeMaster = B2E.EmployeeMaster
ORDER BY B2E.Section, B2E.NameLegal;";

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

                da.Fill(ds, "ReportingStructure");

                if (ds.Tables["ReportingStructure"].Rows.Count > 0)
                {
                    reportingStructureList = ds.Tables["ReportingStructure"].AsEnumerable().Select(r => new KeppelLand.Entities.ReportingStructure
                    {
                        EmployeeMaster = r.Field<string>("EmployeeMaster"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        ApproverId = r.Field<string>("ApproverId")

                    }).ToList();
                }
            }

            return reportingStructureList;
        }

        public static void AddParameters(SqlParameterCollection collection, string parameterName, string nullableType)
        {
            if (nullableType == null)
            {
                collection.AddWithValue(parameterName, DBNull.Value);
            }
            else
            {
                collection.AddWithValue(parameterName, nullableType);
            }
        }

        public static bool UpdateReportingStructure(string employeeMaster, string recommenderId, string approverId)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.DataSet ds = new DataSet();
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = "SELECT * FROM ReportingStructure WHERE EmployeeMaster = @EmployeeMaster;";
                AddParameters(cmd.Parameters, "@EmployeeMaster", employeeMaster);
                da.Fill(ds, "ReportingStructure");

                conn.Open();
                using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
                {
                    cmd.Transaction = sqlTran;

                    cmd.CommandText = "UPDATE Claim SET RecommenderId  = @Recommender WHERE EntryOwner = @EmployeeMaster AND Status = 1;";
                    cmd.Parameters.Clear();
                    AddParameters(cmd.Parameters, "@EmployeeMaster", employeeMaster);
                    AddParameters(cmd.Parameters, "@Recommender", string.IsNullOrEmpty(recommenderId) ? null : recommenderId);
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "UPDATE Claim SET ApproverId  = @ApproverId WHERE EntryOwner = @EmployeeMaster AND Status = 3;";
                    cmd.Parameters.Clear();
                    AddParameters(cmd.Parameters, "@EmployeeMaster", employeeMaster);
                    AddParameters(cmd.Parameters, "@ApproverId", string.IsNullOrEmpty(approverId) ? null : approverId);

                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "UPDATE ReportingStructure SET RecommenderId1 = @Recommender, ApproverId1 = @ApproverId WHERE EmployeeMaster = @EmployeeMaster;";
                    cmd.Parameters.Clear();
                    AddParameters(cmd.Parameters, "@EmployeeMaster", employeeMaster);
                    AddParameters(cmd.Parameters, "@Recommender", string.IsNullOrEmpty(recommenderId) ? null : recommenderId);
                    AddParameters(cmd.Parameters, "@ApproverId", string.IsNullOrEmpty(approverId) ? null : approverId);
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "UPDATE Claim SET Status = 3 WHERE EntryOwner = @EmployeeMaster AND Status = 1 AND RecommenderId IS NULL;";
                    cmd.Parameters.Clear();
                    AddParameters(cmd.Parameters, "@EmployeeMaster", employeeMaster);
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "UPDATE Claim SET Status = 5 WHERE EntryOwner = @EmployeeMaster AND Status = 3 AND ApproverId IS NULL;";
                    cmd.Parameters.Clear();
                    AddParameters(cmd.Parameters, "@EmployeeMaster", employeeMaster);
                    cmd.ExecuteNonQuery();

                    sqlTran.Commit();
                }


                
                result = true;
            }

            return result;
        }

        public static bool UnlockReportingPeriod()
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {

                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                conn.Open();

                cmd.CommandText = @"
UPDATE  Claim
SET     Status = @NewStatus,
        CHAR = 'Y'
WHERE   Status = @OldStatus
        AND ApproveDate < (SELECT EndDate FROM ReportingPeriod WHERE Locked = 1)
        AND ClaimDate < (SELECT CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,EndDate,112), 0, 7) + '01', 112) FROM ReportingPeriod WHERE Locked = 1);";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.EXPORT_TO_SAP);
                cmd.Parameters.AddWithValue("@OldStatus", KeppelLand.Entities.ClaimStatus.CLEARED_BY_HR);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "UPDATE ReportingPeriod SET Locked = 0, LockedDate = @LockedDate WHERE Locked = 1;";
                cmd.Parameters.AddWithValue("@LockedDate", KeppelLand.Helpers.UserIdentityHelper.GetInputDate());
                cmd.ExecuteNonQuery();

                result = true;
            }

            return result;
        }
    }
}